#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
SQL查询管理模块
负责SQL查询处理、历史记录和结果缓存
"""

import json
import logging
import time
import threading
from pathlib import Path
from typing import Dict, List, Tuple, Optional, Union, Any

import sqlparse
from PySide6.QtCore import QObject, Signal, QTimer, QMetaObject, Qt, Slot

from core.db_engine import get_db_engine
from config.constants import QUERY_TIMEOUT, CONFIG_DIR, MAX_RESULTS_PER_PAGE


# 初始化日志记录器
logger = logging.getLogger("query_manager")


class QueryResult:
    """查询结果类"""
    
    def __init__(self, success: bool, data: Optional[Any] = None, 
                error: Optional[str] = None, duration: float = 0.0,
                row_count: int = 0, query_id: Optional[str] = None):
        """初始化查询结果
        
        Args:
            success: 查询是否成功
            data: 查询结果数据
            error: 错误消息
            duration: 查询执行时间（秒）
            row_count: 结果行数
            query_id: 查询ID
        """
        self.success = success
        self.data = data
        self.error = error
        self.duration = duration
        self.row_count = row_count
        self.query_id = query_id
        self.timestamp = time.time()
        
    def to_dict(self) -> Dict[str, Any]:
        """转换为字典表示
        
        Returns:
            字典表示
        """
        return {
            "success": self.success,
            "error": self.error,
            "duration": self.duration,
            "row_count": self.row_count,
            "query_id": self.query_id,
            "timestamp": self.timestamp,
            # 不包括数据，过大
        }


class QueryManager(QObject):
    """SQL查询管理类"""
    
    # 信号定义
    query_started = Signal(str)  # 查询ID
    query_completed = Signal(QueryResult)  # 查询结果
    query_progress = Signal(str, int, int)  # 查询ID, 当前进度, 总进度
    query_error = Signal(str, str)  # 查询ID, 错误消息
    query_timeout = Signal(str)  # 查询ID
    
    # 新增内部信号，用于线程间通信
    _stop_timer_signal = Signal(str)  # 查询ID
    _query_finished_signal = Signal(str, object)  # 查询ID, 结果
    _query_exception_signal = Signal(str, str, float)  # 查询ID, 错误消息, 开始时间
    
    def __init__(self):
        """初始化查询管理器"""
        super().__init__()
        
        # 数据库引擎实例
        self.db_engine = get_db_engine()
        
        # 查询相关集合
        self.active_queries = {}  # 查询ID -> 线程
        self.query_timeouts = {}  # 查询ID -> 定时器
        self.query_results = {}   # 查询ID -> 结果
        self.query_history = []   # 历史记录
        
        # 历史记录文件路径
        self.query_history_path = CONFIG_DIR / "query_history.json"

        # 连接内部信号
        self._stop_timer_signal.connect(self._stop_timer_slot)
        self._query_finished_signal.connect(self._handle_query_finished)
        self._query_exception_signal.connect(self._handle_query_exception)
        
        # 加载查询历史
        self.query_history = self._load_query_history()
        
    def _load_query_history(self) -> List[Dict[str, Any]]:
        """加载查询历史记录
        
        Returns:
            查询历史记录列表
        """
        if not self.query_history_path.exists():
            return []
            
        try:
            with open(self.query_history_path, 'r', encoding='utf-8') as f:
                return json.load(f)
        except Exception as e:
            logger.error(f"加载查询历史记录失败: {str(e)}")
            return []
            
    def _save_query_history(self) -> None:
        """保存查询历史记录"""
        try:
            # 确保目录存在
            self.query_history_path.parent.mkdir(parents=True, exist_ok=True)
            
            with open(self.query_history_path, 'w', encoding='utf-8') as f:
                json.dump(self.query_history, f, ensure_ascii=False, indent=2)
        except Exception as e:
            logger.error(f"保存查询历史记录失败: {str(e)}")
            
    def _add_to_history(self, query: str, result: QueryResult) -> None:
        """添加查询到历史记录
        
        Args:
            query: SQL查询文本
            result: 查询结果
        """
        # 构建历史记录条目
        history_item = {
            "query_id": result.query_id,
            "query_text": query,
            "timestamp": result.timestamp,
            "formatted_time": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(result.timestamp)),
            "duration": result.duration,
            "success": result.success,
            "row_count": result.row_count if result.success else 0,
            "error": result.error if not result.success else None
        }
        
        # 添加到历史
        self.query_history.insert(0, history_item)
        
        # 限制历史记录数量
        if len(self.query_history) > 100:
            self.query_history = self.query_history[:100]
            
        # 保存历史
        self._save_query_history()
    
    def format_query(self, query: str) -> str:
        """格式化SQL查询
        
        Args:
            query: 原始SQL查询
            
        Returns:
            格式化后的SQL查询
        """
        try:
            return sqlparse.format(
                query,
                reindent=True,
                keyword_case='upper',
                identifier_case='lower',
                strip_comments=False
            )
        except Exception as e:
            logger.warning(f"SQL格式化失败: {str(e)}")
            return query
            
    def execute_query(self, query: str, timeout: Optional[int] = None) -> str:
        """执行SQL查询
        
        Args:
            query: SQL查询语句
            timeout: 超时时间（秒），默认使用配置值
            
        Returns:
            查询ID
        """
        # 生成查询ID
        query_id = f"query_{int(time.time() * 1000)}"
        
        # 使用默认超时时间
        if timeout is None:
            timeout = QUERY_TIMEOUT
            
        # 创建并启动查询线程
        query_thread = threading.Thread(
            target=self._execute_query_thread,
            args=(query_id, query),
            daemon=True
        )
        
        # 保存到活动查询
        self.active_queries[query_id] = query_thread
        
        # 设置超时定时器
        timer = QTimer(self)
        timer.setSingleShot(True)
        timer.timeout.connect(lambda: self._handle_query_timeout(query_id))
        timer.start(timeout * 1000)  # 毫秒
        self.query_timeouts[query_id] = timer
        
        # 发出开始信号
        self.query_started.emit(query_id)
        
        # 启动线程
        query_thread.start()
        
        return query_id
    
    @Slot(str)
    def _stop_timer_slot(self, query_id: str):
        """停止查询定时器（在主线程中运行）
        
        Args:
            query_id: 查询ID
        """
        if query_id in self.query_timeouts:
            self.query_timeouts[query_id].stop()
            del self.query_timeouts[query_id]
    
    @Slot(str, object)
    def _handle_query_finished(self, query_id: str, result: QueryResult):
        """处理查询结束（在主线程中运行）
        
        Args:
            query_id: 查询ID
            result: 查询结果
        """
        # 保存结果
        self.query_results[query_id] = result
        
        # 添加到历史
        self._add_to_history(result.query_text, result)
        
        # 清理活动查询
        if query_id in self.active_queries:
            del self.active_queries[query_id]
        
        # 发出完成信号
        self.query_completed.emit(result)
    
    @Slot(str, str, float)
    def _handle_query_exception(self, query_id: str, error_msg: str, start_time: float):
        """处理查询异常（在主线程中运行）
        
        Args:
            query_id: 查询ID
            error_msg: 错误消息
            start_time: 开始时间
        """
        # 创建错误结果
        error_result = QueryResult(
            success=False,
            error=error_msg,
            duration=time.time() - start_time,
            query_id=query_id
        )
        
        # 保存结果
        self.query_results[query_id] = error_result
        
        # 清理活动查询
        if query_id in self.active_queries:
            del self.active_queries[query_id]
        
        # 发出错误信号
        self.query_error.emit(query_id, error_msg)
        
    def _execute_query_thread(self, query_id: str, query: str) -> None:
        """查询执行线程函数
        
        Args:
            query_id: 查询ID
            query: SQL查询文本
        """
        try:
            # 格式化查询
            formatted_query = self.format_query(query)
            
            # 执行查询
            start_time = time.time()
            success, data_or_error, duration = self.db_engine.execute_query(formatted_query)
            
            # 通过信号通知主线程停止定时器
            self._stop_timer_signal.emit(query_id)
            
            # 处理结果
            if success:
                row_count = len(data_or_error)
                result = QueryResult(
                    success=True,
                    data=data_or_error,
                    duration=duration,
                    row_count=row_count,
                    query_id=query_id
                )
                # 保存查询文本用于历史记录
                result.query_text = query
            else:
                result = QueryResult(
                    success=False,
                    error=data_or_error,  # 错误消息
                    duration=time.time() - start_time,
                    query_id=query_id
                )
                result.query_text = query
            
            # 通过信号将结果发送到主线程
            self._query_finished_signal.emit(query_id, result)
            
        except Exception as e:
            logger.error(f"查询执行异常: {str(e)}")
            
            # 通过信号通知主线程停止定时器
            self._stop_timer_signal.emit(query_id)
            
            # 通过信号将异常信息发送到主线程
            self._query_exception_signal.emit(query_id, str(e), start_time)
    
    def _handle_query_timeout(self, query_id: str) -> None:
        """处理查询超时
        
        Args:
            query_id: 查询ID
        """
        # 检查查询是否仍在运行
        if query_id in self.active_queries:
            logger.warning(f"查询超时: {query_id}")
            
            # 无法真正取消线程，但可以标记为已超时
            if query_id in self.query_timeouts:
                del self.query_timeouts[query_id]
                
            # 发出超时信号
            self.query_timeout.emit(query_id)
    
    def cancel_query(self, query_id: str) -> bool:
        """取消查询
        
        Args:
            query_id: 查询ID
            
        Returns:
            是否成功取消
        """
        # 检查查询是否存在且正在运行
        if query_id not in self.active_queries:
            return False
            
        # 取消超时定时器
        if query_id in self.query_timeouts:
            self.query_timeouts[query_id].stop()
            del self.query_timeouts[query_id]
            
        # 无法真正取消线程，但可以标记为已取消
        del self.active_queries[query_id]
        
        # 创建取消结果
        cancel_result = QueryResult(
            success=False,
            error="查询已取消",
            query_id=query_id
        )
        
        # 保存结果
        self.query_results[query_id] = cancel_result
        
        # 发出错误信号
        self.query_error.emit(query_id, "查询已取消")
        
        return True
        
    def get_query_result(self, query_id: str) -> Optional[QueryResult]:
        """获取查询结果
        
        Args:
            query_id: 查询ID
            
        Returns:
            查询结果，如果不存在则返回None
        """
        return self.query_results.get(query_id)
        
    def get_query_history(self, limit: int = 50) -> List[Dict[str, Any]]:
        """获取查询历史记录
        
        Args:
            limit: 最大记录数
            
        Returns:
            查询历史记录列表
        """
        return self.query_history[:limit]
        
    def clear_query_history(self) -> None:
        """清空查询历史记录"""
        self.query_history = []
        self._save_query_history()
    
    def is_query_running(self, query_id: str) -> bool:
        """检查查询是否正在运行
        
        Args:
            query_id: 查询ID
            
        Returns:
            是否正在运行
        """
        return query_id in self.active_queries


# 单例实例
_query_manager_instance = None

def get_query_manager() -> QueryManager:
    """获取查询管理器单例实例
    
    Returns:
        QueryManager实例
    """
    global _query_manager_instance
    if _query_manager_instance is None:
        _query_manager_instance = QueryManager()
    return _query_manager_instance
